使用mysql-proxy 快速实现mysql 集群读写分离

        目前较为常见的mysql读写分离分为两种:

  1. 基于程序代码内部实现:在代码中对select操作分发到从库;其它操作由主库执行;这类方法也是目前生产环境应用最广泛,知名的如DISCUZ X2。优点是性能较好,因为在程序代码中实现,不需要增加额外的设备作为硬件开支。缺点是需要开发人员来实现,运维人员无从下手。

  2. 基于中间代理层实现:我们都知道代理一般是位于客户端和服务器之间,代理服务器接到客户端请求后通过判断然后转发到后端数据库。在这有两个代表性程序

        mysql-proxy:mysql-proxy为mysql开源项目,通过其自带的lua脚本进行sql判断,虽然是mysql官方产品,但是mysql官方并不建议将mysql-proxy用到生产环境。

        amoeba:由陈思儒开发,作者曾就职于阿里巴巴,现就职于盛大。该程序由java语言进行开发,目前只听说阿里巴巴将其用于生产环境。另外,此项目严重缺少维护和推广(作者有个官方博客,很多用户反馈的问题发现作者不理睬)

        经过上述简单的比较,通过程序代码实现mysql读写分离自然是一个不错的选择。但是并不是所有的应用都适合在程序代码中实现读写分离,像大型SNS、B2C这类应用可以在代码中实现,因为这样对程序代码本身改动较小;像一些大型复杂的java应用,这种类型的应用在代码中实现对代码改动就较大了。所以,像这种应用一般就会考虑使用代理层来实现。

        下面我们看一下如何搭建mysql-proxy来实现mysql读写分离

        环境拓扑如下:

        关于mysql、mysql主从的搭建,在此不再演示,如下的操作均在mysql-proxy(192.168.1.200)服务器进行

一、安装mysql-proxy

1、安装lua (mysql-proxy需要使用lua脚本进行数据转发)

1
2
tar zxvf lua-5.1.4.tar.gz
cd lua-5.1.4

        vi Makefile,修改INSTALL_TOP= /usr/local/lua

1
2
make posix
make install

2、安装libevent

1
2
3
4
tar zxvf libevent-2.0.8-rc.tar.gz
cd libevent-2.0.8-rc
./configure --prefix=/usr/local/libevent
make && make install

3、安装check

1
2
3
4
tar zxvf check-0.9.8.tar.gz
cd check-0.9.8
./configure && make && make install

4、安装mysql客户端

1
2
3
4
tar zxvf mysql-5.0.92.tar.gz
cd mysql-5.0.92
./configure
--without-server && make && make install

5、设置环境变量

        (安装mysql-proxy所需变量)

1
2
3
4
5
6
7
8
9
10
vi /etc/profile
export
LUA_CFLAGS="-I/usr/local/lua/include" LUA_LIBS="-L/usr/local/lua/lib -llua -ldl"
LDFLAGS="-L/usr/local/libevent/lib -lm"
export
CPPFLAGS="-I/usr/local/libevent/include"
export
CFLAGS="-I/usr/local/libevent/include"
# source /etc/profile

6、安装mysql-proxy

1
2
3
4
tar zxvf mysql-proxy-0.6.0.tar.gz
cd mysql-proxy-0.6.0
./configure --prefix=/usr/local/mysql-proxy --with-mysql --with-lua
make && make install

7、启动mysql-proxy

        本次对两台数据库实现了读写分离;mysql-master为可读可写,mysql-slave为只读

1
2
3
4
5
/usr/local/mysql-proxy/sbin/mysql-proxy
--proxy-backend-addresses=192.168.1.201:3306
--proxy-read-only-backend-addresses=192.168.1.202:3306
--proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua
&

        注:如果正常情况下启动后终端不会有任何提示信息,mysql-proxy启动后会启动两个端口4040和4041,4040用于SQL转发,4041用于管理mysql-proxy。如有多个mysql-slave可以依次在后面添加

二、测试

1、连接测试

        因为默认情况下mysql数据库不允许用户在远程连接

1
2
3
mysql>grant
all privileges on *.* to identified by '123456';
mysql>flush privileges;

        客户端连接

1
#mysql -uroot -p123456 -h192.168.1.200 -P4040

2、读写分离测试

        为了测试出mysql读写分离的真实性,在测试之前,需要开启两台mysql的log功能,然后在mysql-slave服务器停止复制

  • 在两台mysql配置文件my.cnf中加入log=query.log,然后重启
  • 在mysql-slave上执行SQL语句stop slave
  • 在两台mysql上执行#tail -f /usr/local/mysql/var/query.log
  • 在客户端上连接mysql(三个连接以上),然后执行create、select等SQL语句,观察两台mysql的日志有何变化

        注:生产环境中除了进行程序调试外,其它不要开启mysql查询日志,因为查询日志记录了客户端的所有语句,频繁的IO操作将会导致mysql整体性能下降

        总结:在上述环境中,mysql-proxy和mysql-master、mysql-slave三台服务器均存在单点故障。如果在可用性要求较高的场合,单点隐患是绝对不允许的。为了避免mysql-proxy单点隐患有两种方法,一种方法是mysql-proxy配合keepalived做双机,另一种方法是将mysql-proxy和应用服务安装到同一台服务器上;为了避免mysql-master单点故障可以使用DRBD+heartbear做双机;避免mysql-slave单点故障增加多台mysql-slave即可,因为mysql-proxy会自动屏蔽后端发生故障的mysql-slave。

附: mysql-proxy LUA 读写分离脚本代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
--[[
--
-- author : KDr2
-- version 0.01
-- SYNOPSIS:
---
1.维护了一个连接池
--- 2.读写分离,简单的将select开头的语句放到slave上执行
---
3.事务支持,所有事务放到master上执行,事务中不更改连接
--- 4.简单日志
--
--]]
--- config vars
local min_idle_connections = 4
local
max_idle_connections = 8
local log_level=1
local encoding="utf8"
---
end of config
事务标识,在事务内不归还连接
local
transaction_flags={}
setmetatable(transaction_flags,{__index=function()
return 0 end})
-- log system
log={
level={debug=1,info=2,warn=3,error=4},
funcs={"debug","info","warn","error"},
}
function log.log(level,m)
if level >= log_level then
local msg="[" .. os.date("%Y-%m-%d %X")
.."] ".. log.funcs[level] .. ": " .. tostring(m)
print(msg) -- TODO
write msg into a log file.
end
end
for i,v in ipairs(log.funcs)
do
log[v]=function(m) log.log(log.level[v],m) end
end
-- connect to server
function connect_server()
log.info(" starting
connect_server ... ")
local least_idle_conns_ndx = 0
local
least_idle_conns = 0
for i = 1, #proxy.backends do
local s
= proxy.backends[i]
local pool = s.pool
local cur_idle =
pool.users[""].cur_idle_connections
log.debug("[".. s.address .."].connected_clients = " ..
s.connected_clients)
log.debug("[".. s.address .."].idling_connections
= " .. cur_idle)
log.debug("[".. s.address .."].type = " ..
s.type)
log.debug("[".. s.address .."].state = " .. s.state)
if s.state ~= proxy.BACKEND_STATE_DOWN then
-- try to
connect to each backend once at least
if cur_idle == 0
then
proxy.connection.backend_ndx = i
log.info("server [".. proxy.backends[i].address .."] open new
connection")
return
end
-- try to open at
least min_idle_connections
if least_idle_conns_ndx == 0
or
( cur_idle < min_idle_connections and
cur_idle < least_idle_conns ) then
least_idle_conns_ndx =
i
least_idle_conns = cur_idle
end
end
end
if least_idle_conns_ndx > 0 then
proxy.connection.backend_ndx
= least_idle_conns_ndx
end
if proxy.connection.backend_ndx
> 0 then
local s =
proxy.backends[proxy.connection.backend_ndx]
local pool = s.pool
local cur_idle = pool.users[""].cur_idle_connections
if cur_idle >= min_idle_connections then
-- we have 4
idling connections in the pool, that's good enough
log.debug("using
pooled connection from: " .. proxy.connection.backend_ndx)
return
proxy.PROXY_IGNORE_RESULT
end
end
-- open a new connection
log.info("opening new connection on: " ..
proxy.backends[proxy.connection.backend_ndx].address)
end
---
-- auth.packet is the packet
function read_auth_result( auth )
if
auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
-- 连接正常
proxy.connection.backend_ndx = 0
elseif auth.packet:byte() ==
proxy.MYSQLD_PACKET_EOF then
-- we received either a
-- *
MYSQLD_PACKET_ERR and the auth failed or
-- * MYSQLD_PACKET_EOF which
means a OLD PASSWORD (4.0) was sent
log.error("(read_auth_result) ...
not ok yet");
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR
then
log.error("auth failed!")
end
end
---
-- read/write splitting
function read_query( packet )
log.debug("[read_query]")
log.debug("authed backend = " ..
proxy.connection.backend_ndx)
log.debug("used db = " ..
proxy.connection.client.default_db)
if packet:byte() == proxy.COM_QUIT then
proxy.response =
{
type = proxy.MYSQLD_PACKET_OK,
}
return
proxy.PROXY_SEND_RESULT
end
if proxy.connection.backend_ndx == 0 then
local
is_read=(string.upper(packet:sub(2))):match("^SELECT")
local
target_type=proxy.BACKEND_TYPE_RW
if is_read then
target_type=proxy.BACKEND_TYPE_RO end
for i = 1, #proxy.backends
do
local s = proxy.backends[i]
local pool = s.pool
local cur_idle =
pool.users[proxy.connection.client.username].cur_idle_connections
if cur_idle > 0 and
s.state ~=
proxy.BACKEND_STATE_DOWN and
s.type == target_type
then
proxy.connection.backend_ndx = i
break
end
end
end
-- sync the client-side
default_db with the server-side default_db
if proxy.connection.server and
proxy.connection.client.default_db ~= proxy.connection.server.default_db
then
local server_db=proxy.connection.server.default_db
local
client_db=proxy.connection.client.default_db
local default_db=
(#client_db > 0) and client_db or server_db
if #default_db > 0
then
proxy.queries:append(2, string.char(proxy.COM_INIT_DB) ..
default_db)
proxy.queries:append(2, string.char(proxy.COM_QUERY) ..
"set names '" .. encoding .."'")
log.info("change database to " ..
default_db);
end
end
if proxy.connection.backend_ndx > 0
then
log.debug("Query[" .. packet:sub(2) .. "] Target is [" ..
proxy.backends[proxy.connection.backend_ndx].address .."]")
end
proxy.queries:append(1, packet)
return proxy.PROXY_SEND_QUERY
end
---
-- as long as we are in a transaction keep the connection
--
otherwise release it so another client can use it
function read_query_result(
inj )
local res = assert(inj.resultset)
local flags =
res.flags
if inj.id ~= 1 then
-- ignore the result of the USE
<default_db>
return proxy.PROXY_IGNORE_RESULT
end
is_in_transaction = flags.in_trans
if flags.in_trans then
transaction_flags[proxy.connection.server.thread_id] =
transaction_flags[proxy.connection.server.thread_id] + 1
elseif
inj.query:sub(2):lower():match("^%s*commit%s*$") or
inj.query:sub(2):lower():match("^%s*rollback%s*$") then
transaction_flags[proxy.connection.server.thread_id] =
transaction_flags[proxy.connection.server.thread_id] - 1
if
transaction_flags[proxy.connection.server.thread_id] < 0 then
transaction_flags[proxy.connection.server.thread_id] = 0 end
end
log.debug("transaction res : " ..
tostring(transaction_flags[proxy.connection.server.thread_id]));
if
transaction_flags[proxy.connection.server.thread_id]==0 or
transaction_flags[proxy.connection.server.thread_id] == nil then
--
isnot in a transaction, need to release the backend
proxy.connection.backend_ndx = 0
end
end
---
-- close the connections if we have enough connections in the
pool
--
-- @return nil - close connection
-- IGNORE_RESULT - store
connection in the pool
function disconnect_client()
log.debug("[disconnect_client]")
if proxy.connection.backend_ndx == 0
then
for i = 1, #proxy.backends do
local s =
proxy.backends[i]
local pool = s.pool
local cur_idle =
pool.users[proxy.connection.client.username].cur_idle_connections
if s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle
> max_idle_connections then
-- try to disconnect a
backend
proxy.connection.backend_ndx = i
log.info("[".. proxy.backends[i].address .."] closing connection, idling: " ..
cur_idle)
return
end
end
return
proxy.PROXY_IGNORE_RESULT
end
end